<?php

///////////////////////////////////////////////////////////////////////////////
/**
 * IConnector implementation for the PHP ODBC database extension.
 *
 * System requirements:
 * <ul>
 * <li>PHP 5</li>
 * <li>The {@link PHP_MANUAL#book.uodbc ODBC database extension}</li>
 * </ul>
 *
 * This library is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * The Connector library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * {@link http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License}
 * for more details.
 *
 * @author Per Egil Roksvaag
 * @copyright 2009 Per Egil Roksvaag
 * @license http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License
 * @package connector
 * @version 2.0.0
 */

///////////////////////////////////////////////////////////////////////////////
/**
 * Includes the parent class.
 */

require_once("BaseConnector.php");

///////////////////////////////////////////////////////////////////////////////
/**
 * IConnector implementation for the PHP ODBC database extension.
 *
 * @package connector
 */

class OdbcConnector extends BaseConnector implements IConnector
{
	///////////////////////////////////////////////////////////////////////////
	/**
	 * This option sets the type of cursor to be used for this odbc connection.
	 *
	 * This option is not normally needed, but can be useful for working
	 * around problems with some odbc drivers.
	 *
	 * This option can only be used as a <b>constructor</b> option.
	 * Valid values are the {@link odbc_connect() odbc cursor types}.
	 * The default value is <b>null</b> (odbc default cursor).
	 */

	const CONN_CURSOR = "connCursor";

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Contains the driver name of the current ODBC data source.
	 */

	const DRIVER = "driver";

	///////////////////////////////////////////////////////////////////////////
	/**#@+
 	* Currently supported ODBC drivers.
 	*/

	const DRIVER_MSSQL_NATIVE = "SQL Native Client";
	const DRIVER_MSSQL_SERVER = "SQL Server";
	const DRIVER_MYSQL = "MySQL ODBC 5.1 Driver";

	/**#@-*/
	///////////////////////////////////////////////////////////////////////////
	/**
	 * Open a connection to a ODBC data source and set global options.
	 *
	 * @param array $connection An associated array of connection settings, like database and user name.
	 * @param array $options An associated array of global options for the resulting instance.
	 * @return OdbcConnector
	 */

	public function OdbcConnector($connection, $options = array())
	{
		if($this->open($connection))
		{
			switch($this->lookup(self::DRIVER, $options))
			{
				case self::DRIVER_MSSQL_NATIVE: break;
				case self::DRIVER_MSSQL_SERVER: break;
				case self::DRIVER_MYSQL: break;
			}
			parent::BaseConnector($options);
		}
		else if($this->lookup("throwException", $options))
		{
			throw new Exception("OdbcConnector error: Connection failed.");
		}
		else if($this->lookup(self::LOG_ERROR, $options))
		{
			$log = "OdbcConnector error: Connection failed in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
		}
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Open a database connection.
	 * @param array $connection An associated array of connection settings, like host and user name.
	 * @return bool <var>true</var>, if a database connection was successfully opened, <var>false</var> otherwise.
	 */

	protected function open($connection)
	{
		$database = $this->lookup(self::CONN_DATABASE, $connection);
		$username = $this->lookup(self::CONN_USERNAME, $connection);
		$password = $this->lookup(self::CONN_PASSWORD, $connection);
		$cursor = $this->lookup(self::CONN_CURSOR, $connection);

		if($cursor)
		{
			$this->conn = $this->lookup(self::CONN_PERSISTENT, $connection)
				? odbc_pconnect($database, $username, $password, $cursor)
				: odbc_connect($database, $username, $password, $cursor);
		}
		else
		{
			$this->conn = $this->lookup(self::CONN_PERSISTENT, $connection)
				? odbc_pconnect($database, $username, $password)
				: odbc_connect($database, $username, $password);
		}

		$this->options[self::DRIVER] = $this->conn
			? $this->driver($database)
			: null;

		return is_resource($this->conn);
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Send a SQL SELECT query to the database and get the query result.
	 *
	 * @see IConnector::select().
	 * @param string $query A SQL query to execute on a database.
	 * @param array $param An associated array of values to be used in the $query.
	 * @param array $map An array of type definitions for the <var>$param</var> values.
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @throws Exception when $param doesn't match the type definition $map.
	 * @return array The query result as a table (array of associated arrays).
	 */

	public function select($query, $param = array(), $map = array(), $options = array())
	{
		$param = TypeValidator::check($param, $map);
		$query = $this->bind($query, $param, $stack, $options);
		$query = $this->build($query, $options);
		$hash = $this->getHash($query, $stack, $options);

		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log1 = "OdbcConnector debug: Select query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
		}
		if($this->getCache($hash, $table, $options))
		{
			return $table;
		}
		if($this->lookup(self::PARAM_QUERIES, $options))
		{
			$stmt = odbc_prepare($this->conn, $query);
			$success = odbc_execute($stmt, $stack);
		}
		else
		{
			$stmt = odbc_exec($this->conn, $query);
			$success = (bool)$stmt;
		}
		if($success)
		{
			do $table[] = $this->fetch($stmt, $options);
			while(odbc_next_result($stmt));
			odbc_free_result($stmt);

			if(count($table) == 1) $table = current($table);
			$this->setCache($hash, $table, $options);
			return $table;
		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log1 = "OdbcConnector error: Select query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
			error_log("OdbcConnector error: ".odbc_errormsg($this->conn));
		}
		return false;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Send a SQL INSERT query to the database and get the IDENTITY ID
	 * generated from the last INSERT operation (if any).
	 *
	 * @see IConnector::insert().
	 * @param string $query A SQL query to execute on a database.
	 * @param array $param An associated array of values to be used in the $query.
	 * @param array $map An array of type definitions for the <var>$param</var> values.
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @throws Exception when $param doesn't match the type definition $map.
	 * @return int The IDENTITY ID of the last inserted row.
	 */

	public function insert($query, $param = array(), $map = array(), $options = array())
	{
		$param = TypeValidator::check($param, $map);
		$query = $this->bind($query, $param, $stack, $options);
		$driver = $this->lookup(self::DRIVER, $options);

		switch($driver)
		{
			case self::DRIVER_MSSQL_NATIVE:
			case self::DRIVER_MSSQL_SERVER:
				$query = rtrim($query, ";").";".LB."SELECT SCOPE_IDENTITY();";
				break;
		}
		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log1 = "OdbcConnector debug: Insert query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
		}
		if($this->lookup(self::PARAM_QUERIES, $options))
		{
			$stmt = odbc_prepare($this->conn, $query);
			$success = odbc_execute($stmt, $stack);
		}
		else
		{
			$stmt = odbc_exec($this->conn, $query);
			$success = (bool)$stmt;
		}
		if($success)
		{
			do
			{
				if(odbc_num_fields($stmt) && odbc_fetch_into($stmt, $row))
				{
					$key = current($row);
				}
			}
			while(odbc_next_result($stmt));
			odbc_free_result($stmt);
			return isset($key) ? (int)$key : null;
 		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log1 = "OdbcConnector error: Insert query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
			error_log("OdbcConnector error: ".odbc_errormsg($this->conn));
		}
		return false;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Send a SQL UPDATE query to the database and get the number of rows
	 * updates by the query.
	 *
	 * @see IConnector::update().
	 * @param string $query A SQL query to execute on a database.
	 * @param array $param An associated array of values to be used in the $query.
	 * @param array $map An array of type definitions for the <var>$param</var> values.
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @throws Exception when $param doesn't match the type definition $map.
	 * @return int The number of rows updates by the query.
	 */

	public function update($query, $param = array(), $map = array(), $options = array())
	{
		$param = TypeValidator::check($param, $map);
		$query = $this->bind($query, $param, $stack, $options);

		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log1 = "OdbcConnector debug: Update query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
		}
		if($this->lookup(self::PARAM_QUERIES, $options))
		{
			$stmt = odbc_prepare($this->conn, $query);
			$success = odbc_execute($stmt, $stack);
		}
		else
		{
			$stmt = odbc_exec($this->conn, $query);
			$success = (bool)$stmt;
		}
		if($success)
		{
			$count = odbc_num_rows($stmt);
			odbc_free_result($stmt);
			return $count;
 		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log1 = "OdbcConnector error: Update query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
			error_log("OdbcConnector error: ".odbc_errormsg($this->conn));
		}
		return false;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Send a SQL DELETE query to the database and get the number of rows
	 * deleted by the query.
	 *
	 * @see IConnector::delete().
	 * @param string $query A SQL query to execute on a database.
	 * @param array $param An associated array of values to be used in the $query.
	 * @param array $map An array of type definitions for the <var>$param</var> values.
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @throws Exception when $param doesn't match the type definition $map.
	 * @return int The number of rows deleted by the query.
	 */

	public function delete($query, $param = array(), $map = array(), $options = array())
	{
		$param = TypeValidator::check($param, $map);
		$query = $this->bind($query, $param, $stack, $options);

		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log1 = "OdbcConnector debug: Delete query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
		}
		if($this->lookup(self::PARAM_QUERIES, $options))
		{
			$stmt = odbc_prepare($this->conn, $query);
			$success = odbc_execute($stmt, $stack);
		}
		else
		{
			$stmt = odbc_exec($this->conn, $query);
			$success = (bool)$stmt;
		}
		if($success)
		{
			$count = odbc_num_rows($stmt);
			odbc_free_result($stmt);
			return $count;
		}
		if($this->lookup(self::LOG_ERROR, $options))
		{
			$log1 = "OdbcConnector error: Delete query in ";
			$log2 = $stack ? LB."Params: ".@implode(", ", $stack) : "";
			error_log($log1.$_SERVER["SCRIPT_FILENAME"].LB.$query.$log2);
			error_log("OdbcConnector error: ".odbc_errormsg($this->conn));
		}
		return false;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Begins a transaction on the current connection.
	 * The current transaction includes all statements on the connection that
	 * were executed after the call to transaction() and before any calls
	 * to rollback() or commit().
	 *
	 * @see IConnector::transaction().
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @return bool true if the transaction was successfully begun, false otherwise.
	 */
	
	public function transaction($options = array())
	{
		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log = "OdbcConnector debug: Begin transaction in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
		}
		if(odbc_autocommit($this->conn, false))
		{
			return true;
		}
		else if($this->lookup(self::LOG_ERROR, $options))
		{
			$log = "OdbcConnector error: Begin transaction in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
			error_log("OdbcConnector error: ".sqlsrv_errors());
		}
	}
	
	///////////////////////////////////////////////////////////////////////////
	/**
	 * Commits the current transaction on the current connection.
	 * The current transaction includes all statements on the connection that
	 * were executed after the call to transaction() and before any calls
	 * to rollback() or commit().
	 *
	 * @see IConnector::commit().
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @return bool true if the transaction was successfully committed, false otherwise.
	 */
	
	public function commit($options = array())
	{
		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log = "OdbcConnector debug: Commit in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
		}
		if(odbc_commit($this->conn))
		{
			return true;
		}
		else if($this->lookup(self::LOG_ERROR, $options))
		{
			$log = "OdbcConnector error: Commit in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
			error_log("OdbcConnector error: ".sqlsrv_errors());
		}
	}
	
	///////////////////////////////////////////////////////////////////////////
	/**
	 * Rolls back the current transaction on the current connection. 
	 * The current transaction includes all statements on the connection that
	 * were executed after the call to transaction() and before any calls
	 * to rollback() or commit().
	 *
	 * @see IConnector::rollback().
	 * @param array $options An associated array of options, see the {@tutorial connector.pkg#options.element}.
	 * @return bool true if the transaction was successfully rolled back, false otherwise.
	 */
	
	public function rollback($options = array())
	{
		if($this->lookup(self::LOG_DEBUG, $options))
		{
			$log = "OdbcConnector debug: Rollback in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
		}
		if(odbc_rollback($this->conn))
		{
			return true;
		}
		else if($this->lookup(self::LOG_ERROR, $options))
		{
			$log = "OdbcConnector error: Rollback in ";
			error_log($log.$_SERVER["SCRIPT_FILENAME"]);
			error_log("OdbcConnector error: ".sqlsrv_errors());
		}
	}
	
	///////////////////////////////////////////////////////////////////////////
	/**
	 * Add a TOP x statement to a SQL SELECT query when the
	 * {@link IConnector::RESULT_LENGTH} is set.
	 *
	 * @param string $query A SQL query to execute on a database.
	 * @param array $options An associated array of options.
	 * @return string A modified SQL query.
	 */

	protected function build($query, $options = array())
	{
		$driver = $this->lookup(self::DRIVER, $options);
		$offset = $this->lookup(self::RESULT_OFFSET, $options);
		$length = $this->lookup(self::RESULT_LENGTH, $options);
				
		switch($driver)
		{
			case self::DRIVER_MSSQL_NATIVE:
			case self::DRIVER_MSSQL_SERVER: if(is_int($length))
			{
				$number = $offset + $length;
				$pattern = "/\A(SELECT)([\s]+(ALL|DISTINCT))?\b/i";
				$query = preg_replace($pattern, "$0 TOP {$number}", $query);
			}
			break;
			case self::DRIVER_MYSQL: if($offset || is_int($length))
			{
				if(is_null($length)) $length = PHP_INT_MAX;
				$query.= LB."LIMIT  {$offset}, {$length}";
			}
			break;
		}
		return $query;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Fetch multiple rows of a query result.
	 *
	 * If the {@link IConnector::RESULT_LENGTH} or {@link IConnector::RESULT_OFFSET}
	 * options are set, some rows are omitted from the beginning and/or the end
	 * of the query result.
	 * If the {@link IConnector::RESULT_KEY_FIELD} option is set, the
	 * resulting table is an <b>associated</b> array of rows.
	 *
	 * @param resource $stmt A statement resource corresponding to an executed statement.
	 * @param array $options An associated array of options.
	 * @return array The query result as a table (array of associated arrays).
	 */

	protected function fetch($stmt, $options = array())
	{
		$key = $this->lookup(self::RESULT_KEY_FIELD, $options);
		$offset = $this->lookup(self::RESULT_OFFSET, $options);
		$length = $this->lookup(self::RESULT_LENGTH, $options);
		$number = is_null($length) ? PHP_INT_MAX : $length + $offset;
		$table = array();
		$index = 0;

		while($row = odbc_fetch_array($stmt))
		{
			if($number <= $index) break;
			if($offset > $index++) continue;
			$row = $this->strDecode($row, $options);
			$key ? $table[$row[$key]] = $row : $table[] = $row;
		}
		return $table;
	}

	///////////////////////////////////////////////////////////////////////////
	/**
	 * Get the driver name of a ODBC data source.
	 *
	 * @param string $name The currently used ODBC data source.
	 * @return string The driver name of the ODBC data source.
	 */

	protected function driver($name)
	{
		$row = odbc_data_source($this->conn, SQL_FETCH_FIRST);

		while($row)
		{
			if(strtolower($name) == strtolower($row["server"]))
			{
				return $row["description"];
			}
	        $row = odbc_data_source($this->conn, SQL_FETCH_NEXT);
		}
	}

	///////////////////////////////////////////////////////////////////////////
}

?>